package com.example.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.UserRole;

@Repository
public interface UserRoleDao extends JpaRepository<UserRole, Integer> {

    @Transactional
    @Modifying
    Integer deleteByUserRoleId(Integer userRoleId);

    List<UserRole> findByUserRoleId(Integer userRoleId);

    @Transactional
    @Modifying
    Integer deleteByUserRoleIdIn(List<Integer> listId);

    @Query(nativeQuery = true, value = "SELECT u.user_Id as userId,u.account as userName,u.nickName FROM users u "
            + "WHERE u.user_Id not in (SELECT ur.user_Id FROM user_role ur)")
    List<Map<String, Object>> queryAllUserOfNotRole();

    @Query(nativeQuery = true, value = "SELECT r.role_Id as roleId, r.roleName  FROM role r")
    List<Map<String, Object>> queryAllRoleName();

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,r.roleName,ur.* FROM (users u INNER JOIN user_role ur ON u.user_Id = ur.user_Id)\r\n" +
            "INNER JOIN role r ON r.role_Id = ur.role_Id")
    List<Map<String, Object>> queryAllUserAndRoleInfo();

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,r.roleName,ur.* FROM (users u INNER JOIN user_role ur ON u.user_Id = ur.user_Id)\r\n" +
            "INNER JOIN role r ON r.role_Id = ur.role_Id\r\n" +
            "WHERE u.account like CONCAT('%',:userName,'%')")
    List<Map<String, Object>> findByUserNameLike(@Param("userName") String userName);

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,r.roleName,ur.* FROM (users u INNER JOIN user_role ur ON u.user_Id = ur.user_Id)\r\n" +
            "INNER JOIN role r ON r.role_Id = ur.role_Id\r\n" +
            "WHERE u.nickName like CONCAT('%',:nickName,'%')")
    List<Map<String, Object>> findByNickNameLike(@Param("nickName") String nickName);

    @Query(nativeQuery = true, value = "SELECT u.account as userName,u.nickName,r.roleName,ur.* FROM (users u INNER JOIN user_role ur ON u.user_Id = ur.user_Id)\r\n" +
            "INNER JOIN role r ON r.role_Id = ur.role_Id\r\n" +
            "WHERE r.roleName like CONCAT('%',:roleName,'%')")
    List<Map<String, Object>> findByRoleNameLike(@Param("roleName") String roleName);

}
